<?php
// +----------------------------------------------------------------------
// | ThinkCMF [ WE CAN DO IT MORE SIMPLE ]
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2017 http://www.thinkcmf.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: pl125 <xskjs888@163.com>
// +----------------------------------------------------------------------
namespace api\v1\service;

use api\v1\model\XmglGdModel;
use api\v1\model\XmglXmModel;
use think\facade\Db;
use think\db\Query;

class XmService
{
    /**
     * 项目分页查询
     * @param $filter 查询过滤条件
     * @param $param  查询分页、排序等参数
     * @return \think\Paginator
     * @throws \think\exception\DbException
     */
    public function getXmList($filter, $param)
    {
        $field = 'a.*,u.user_nickname';
        $filedArr = [
            'a.*',
            'u.user_nickname as user_xm',
            'xmjl.user_nickname as xmjl_xm',
            'xs.user_nickname as xs_xm',
            'htlx.name as htlx_mc',
            'gslx.name as gslx_mc',
            'sslx.name as sslx_mc',
            'zt.name as zt_mc',
            'syb.zzjg_mc as syb_mc',
            'pt.zzjg_mc as pt_mc',
        ];

        $m = new XmglXmModel();
        $q = $m->alias('a');
        $q->leftJoin('user u', 'a.user_id = u.id');
        $q->leftJoin('user xmjl', 'a.xmjl_id = xmjl.id');
        $q->leftJoin('user xs', 'a.xs_id = xs.id');
        $q->leftJoin('xmgl_config htlx', 'a.htlx_id = htlx.id');
        $q->leftJoin('xmgl_config gslx', 'a.gslx_id = gslx.id');
        $q->leftJoin('xmgl_config sslx', 'a.sslx_id = sslx.id');
        $q->leftJoin('xmgl_config zt', 'a.zt_id = zt.id');
        $q->leftJoin('xmgl_zzjg syb', 'a.syb_id = syb.id');
        $q->leftJoin('xmgl_zzjg pt', 'a.pt_id = pt.id');

        $pageNumber = empty($param['pageNumber']) ? 1 : $param['pageNumber'];
        $pageSize = empty($param['pageSize']) ? 20 : $param['pageSize'];
        $order = empty($param['order']) ? '' : $param['order'];

        $list = $q->field(implode(",", $filedArr))
            ->where('a.id', 'IN', function (Query $q1) use ($filter, $param) {
                //使用子查询，提高查询效率
                $q1->name('xmgl_xm')->where(function (Query $q2) use ($filter, $param) {
                    //q2->where('a.create_time', '>=', 0);
                    $q2->where('delete_time', 0);

                    //根据项目id查询
                    $xm_id = empty($filter['xm_id']) ? 0 : $filter['xm_id'];
                    if (!empty($xm_id)) {
                        $q2->where(['xm_id', '=', $xm_id]);
                    }
                    //根据项目名称和合同编号关键字查询
                    $keyword = empty($filter['keyword']) ? '' : $filter['keyword'];
                    if (!empty($keyword)) {
                        $q2->where("name like '%$keyword%' or htbh like '%$keyword%'");
                    }
                    //客户名称搜索
                    $htkh = empty($filter['htkh']) ? '' : $filter['htkh'];
                    if (!empty($htkh)) {
                        $q2->where("htkh like '%$htkh%' or zzkh like '%$htkh%'");
                    }

                    //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                    $eq = empty($filter['eq']) ? '' : $filter['eq'];
                    if (!empty($eq)) {
                        $q2->where('htjine', '=', $eq);
                    }
                    //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                    $ge = empty($filter['ge']) ? '' : $filter['ge'];
                    if (!empty($eq)) {
                        $q2->where('htjine', '>=', $ge);
                    }
                    //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                    $le = empty($filter['le']) ? '' : $filter['le'];
                    if (!empty($eq)) {
                        $q2->where('htjine', '<=', $le);
                    }

                    //项目经理查询
                    $xmjl_id = empty($filter['xmjl_id']) ? 0 : $filter['xmjl_id'];
                    if (!empty($xmjl_id)) {
                        $q2->where("xmjl_id = $xmjl_id OR find_in_set($xmjl_id, gcss_id)");
                    }
                    //项目经理包含
                    $xmjl_ids = empty($filter['xmjl_ids']) ? '' : $filter['xmjl_ids'];
                    if (!empty($xmjl_ids)) {
                        $q2->where('xmjl_id', 'in', $xmjl_ids);
                    }

                    //销售查询
                    $xs_id = empty($filter['xs_id']) ? 0 : $filter['xs_id'];
                    if (!empty($xs_id)) {
                        $q2->where("xs_id = $xs_id ");
                    }
                    //销售包含
                    $xs_ids = empty($filter['xs_ids']) ? 0 : $filter['xs_ids'];
                    if (!empty($xs_ids)) {
                        $q2->where('xs_id', 'in', $xs_ids);
                    }

                    //项目组成员查询
                    $gcs_id = empty($filter['gcs_id']) ? 0 : $filter['gcs_id'];
                    if (!empty($gcs_id)) {
                        $q2->where("find_in_set($gcs_id, gcss_id)");
                    }
                    //售前成员查询
                    $shq_id = empty($filter['shq_id']) ? 0 : $filter['shq_id'];
                    if (!empty($shq_id)) {
                        $q2->where("find_in_set($shq_id, shq_ids)");
                    }

                    //合同日期，htrq_ge大于等于日期，htrq_le小于等于日期
                    $htrq_ge = empty($filter['htrq_ge']) ? 0 : strtotime($filter['htrq_ge']);
                    if (!empty($htrq_ge)) {
                        $q2->where('ht_time', '>=', $htrq_ge);
                    }
                    $htrq_le = empty($filter['htrq_le']) ? 0 : strtotime($filter['htrq_le']);
                    if (!empty($htrq_le)) {
                        $q2->where('ht_time', '<=', $htrq_le);
                    }

                    //验收日期
                    $ysrq_ge = empty($filter['ysrq_ge']) ? 0 : strtotime($filter['ysrq_ge']);
                    if (!empty($ysrq_ge)) {
                        $q2->where('ht_time', '>=', $ysrq_ge);
                    }
                    $ysrq_le = empty($filter['ysrq_le']) ? 0 : strtotime($filter['ysrq_le']);
                    if (!empty($ysrq_le)) {
                        $q2->where('ht_time', '<=', $ysrq_le);
                    }

                    //合同类型
                    $htlx_id = empty($filter['htlx_id']) ? '' : $filter['htlx_id'];
                    if (!empty($htlx_id)) {
                        $q2->where('htlx_id', '=', $htlx_id);
                    }
                    //公司类型
                    $gslx_id = empty($filter['gslx_id']) ? '' : $filter['gslx_id'];
                    if (!empty($gslx_id)) {
                        $q2->where('gslx_id', '=', $gslx_id);
                    }
                    //行业类型
                    $hylx_id = empty($filter['hylx_id']) ? '' : $filter['hylx_id'];
                    if (!empty($hylx_id)) {
                        $q2->where('hylx_id', '=', $hylx_id);
                    }
                    //实施类型
                    $sslx_id = empty($filter['sslx_id']) ? '' : $filter['sslx_id'];
                    if (!empty($sslx_id)) {
                        $q2->where('sslx_id', '=', $sslx_id);
                    }

                    //平台
                    $pt_id = empty($filter['pt_id']) ? '' : $filter['pt_id'];
                    if (!empty($pt_id)) {
                        $q2->where('pt_id', '=', $pt_id);
                    }
                    //平台包含
                    $pt_ids = empty($filter['pt_ids']) ? '' : $filter['pt_ids'];
                    if (!empty($pt_ids)) {
                        $q2->where('pt_id', 'in', $pt_ids);
                    }

                    //验收状态
                    $yszt_id = empty($filter['yszt_id']) ? '' : $filter['yszt_id'];
                    if (!empty($yszt)) {
                        $q2->where('zt_id', '=', $yszt_id);
                    }
                    //未验收的项目
                    $myys = empty($filter['myys']) ? '' : $filter['myys'];
                    if (!empty($myys)) {
                        $q2->where('zt_id', '<>', config('xmgl.yszt_ys'));      //config('xmgl.yszt_ys')：已验收
                    }
                    //已验收的项目
                    $yjys = empty($filter['yjys']) ? '' : $filter['yjys'];
                    if (!empty($yjys)) {
                        $q2->where('zt_id', '=', config('xmgl.yszt_ys'));      //config('xmgl.yszt_ys')：已验收
                    }

                    //回款状态
                    $hkzt_id = empty($filter['hkzt_id']) ? '' : $filter['hkzt_id'];
                    if (!empty($yszt)) {
                        $q2->where('hkzt_id', '=', $hkzt_id);
                    }
                })->field('id');
            })
            ->order($order)
            ->paginate([
                'list_rows' => $pageSize,
                'page' => $pageNumber,
            ]);

        //dump($q->getLastSql());

        $rowTotal = $list->total();
        $j = [
            'pageSize'          => $pageSize,
            'pageNumber'        => $pageNumber,
            "totalPage"         => ceil($rowTotal / $pageSize),
            'total'             => $rowTotal,
            'rows'              => $list,
            'msg'               => ''
        ];

        return $list;
    }

    //已废弃 ，更换成使用子查询方式提升查询效率
    public function getXmList1($filter, $param) 
    {
        $field = 'a.*,u.user_nickname';
        $filedArr = [
            'a.*',
            'u.user_nickname as user_xm',
            'xmjl.user_nickname as xmjl_xm',
            'xs.user_nickname as xs_xm',
            'htlx.name as htlx_mc',
            'gslx.name as gslx_mc',
            'sslx.name as sslx_mc',
            'zt.name as zt_mc',
            'syb.zzjg_mc as syb_mc',
            'pt.zzjg_mc as pt_mc',
        ];

        $m = new XmglXmModel();
        $q = $m->alias('a');
        $q->join('user u', 'a.user_id = u.id');
        $q->join('user xmjl', 'a.xmjl_id = xmjl.id');
        $q->join('user xs', 'a.xs_id = xs.id');
        $q->join('xmgl_config htlx', 'a.htlx_id = htlx.id');
        $q->join('xmgl_config gslx', 'a.gslx_id = gslx.id');
        $q->join('xmgl_config sslx', 'a.sslx_id = sslx.id');
        $q->join('xmgl_config zt', 'a.zt_id = zt.id');
        $q->join('xmgl_zzjg syb', 'a.syb_id = syb.id');
        $q->join('xmgl_zzjg pt', 'a.pt_id = pt.id');

        //$limit       = empty($param['limit']) ? 10 : $param['limit'];
        $pageNumber = empty($param['pageNumber']) ? 1 : $param['pageNumber'];
        $pageSize = empty($param['pageSize']) ? 20 : $param['pageSize'];
        $order = empty($param['order']) ? '' : $param['order'];

        $list = $q->field(implode(",", $filedArr))
            //->where('a.create_time', '>=', 0)
            ->where('a.delete_time', 0)
            ->where(function (Query $query) use ($filter) {
                //根据项目id查询
                $xm_id = empty($filter['xm_id']) ? 0 : $filter['xm_id'];
                if (!empty($xm_id)) {
                    $query->where(['xm_id', '=', $xm_id]);
                }
                //根据项目名称和合同编号关键字查询
                $keyword = empty($filter['keyword']) ? '' : $filter['keyword'];
                if (!empty($keyword)) {
                    $query->where("a.name like '%$keyword%' or a.htbh like '%$keyword%'");
                }
                //客户名称搜索
                $htkh = empty($filter['htkh']) ? '' : $filter['htkh'];
                if (!empty($htkh)) {
                    $query->where("a.htkh like '%$htkh%' or a.zzkh like '%$htkh%'");
                }

                //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                $eq = empty($filter['eq']) ? '' : $filter['eq'];
                if (!empty($eq)) {
                    $query->where('a.htjine', '=', $eq);
                }
                //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                $ge = empty($filter['ge']) ? '' : $filter['ge'];
                if (!empty($eq)) {
                    $query->where('a.htjine', '>=', $ge);
                }
                //根据合同金额查询，eq等于，gt大于，lt小于，ge大于等于，le小于等于
                $le = empty($filter['le']) ? '' : $filter['le'];
                if (!empty($eq)) {
                    $query->where('a.htjine', '<=', $le);
                }

                //项目经理查询
                $xmjl_id = empty($filter['xmjl_id']) ? 0 : $filter['xmjl_id'];
                if (!empty($xmjl_id)) {
                    $query->where("a.xmjl_id = $xmjl_id OR find_in_set($xmjl_id, a.gcss_id)");
                }
                //项目经理包含
                $xmjl_ids = empty($filter['xmjl_ids']) ? '' : $filter['xmjl_ids'];
                if (!empty($xmjl_ids)) {
                    $query->where('a.xmjl_id', 'in', $xmjl_ids);
                }

                //销售查询
                $xs_id = empty($filter['xs_id']) ? 0 : $filter['xs_id'];
                if (!empty($xs_id)) {
                    $query->where("a.xs_id = $xs_id ");
                }
                //销售包含
                $xs_ids = empty($filter['xs_ids']) ? 0 : $filter['xs_ids'];
                if (!empty($xs_ids)) {
                    $query->where('a.xs_id', 'in', $xs_ids);
                }

                //项目组成员查询
                $gcs_id = empty($filter['gcs_id']) ? 0 : $filter['gcs_id'];
                if (!empty($gcs_id)) {
                    $query->where("find_in_set($gcs_id, a.gcss_id)");
                }
                //售前成员查询
                $shq_id = empty($filter['shq_id']) ? 0 : $filter['shq_id'];
                if (!empty($shq_id)) {
                    $query->where("find_in_set($shq_id, a.shq_ids)");
                }

                //合同日期，htrq_ge大于等于日期，htrq_le小于等于日期
                $htrq_ge = empty($filter['htrq_ge']) ? 0 : strtotime($filter['htrq_ge']);
                if (!empty($htrq_ge)) {
                    $query->where('a.ht_time', '>=', $htrq_ge);
                }
                $htrq_le = empty($filter['htrq_le']) ? 0 : strtotime($filter['htrq_le']);
                if (!empty($htrq_le)) {
                    $query->where('a.ht_time', '<=', $htrq_le);
                }

                //验收日期
                $ysrq_ge = empty($filter['ysrq_ge']) ? 0 : strtotime($filter['ysrq_ge']);
                if (!empty($ysrq_ge)) {
                    $query->where('a.ht_time', '>=', $ysrq_ge);
                }
                $ysrq_le = empty($filter['ysrq_le']) ? 0 : strtotime($filter['ysrq_le']);
                if (!empty($ysrq_le)) {
                    $query->where('a.ht_time', '<=', $ysrq_le);
                }

                //合同类型
                $htlx_id = empty($filter['htlx_id']) ? '' : $filter['htlx_id'];
                if (!empty($htlx_id)) {
                    $query->where('a.htlx_id', '=', $htlx_id);
                }
                //公司类型
                $gslx_id = empty($filter['gslx_id']) ? '' : $filter['gslx_id'];
                if (!empty($gslx_id)) {
                    $query->where('a.gslx_id', '=', $gslx_id);
                }
                //行业类型
                $hylx_id = empty($filter['hylx_id']) ? '' : $filter['hylx_id'];
                if (!empty($hylx_id)) {
                    $query->where('a.hylx_id', '=', $hylx_id);
                }
                //实施类型
                $sslx_id = empty($filter['sslx_id']) ? '' : $filter['sslx_id'];
                if (!empty($sslx_id)) {
                    $query->where('a.sslx_id', '=', $sslx_id);
                }

                //平台
                $pt_id = empty($filter['pt_id']) ? '' : $filter['pt_id'];
                if (!empty($pt_id)) {
                    $query->where('a.pt_id', '=', $pt_id);
                }
                //平台包含
                $pt_ids = empty($filter['pt_ids']) ? '' : $filter['pt_ids'];
                if (!empty($pt_ids)) {
                    $query->where('a.pt_id', 'in', $pt_ids);
                }

                //验收状态
                $yszt_id = empty($filter['yszt_id']) ? '' : $filter['yszt_id'];
                if (!empty($yszt)) {
                    $query->where('a.zt_id', '=', $yszt_id);
                }
                //未验收的项目
                $myys = empty($filter['myys']) ? '' : $filter['myys'];
                if (!empty($myys)) {
                    $query->where('a.zt_id', '<>', config('xmgl.yszt_ys'));      //config('xmgl.yszt_ys')：已验收
                }
                //已验收的项目
                $yjys = empty($filter['yjys']) ? '' : $filter['yjys'];
                if (!empty($yjys)) {
                    $query->where('a.zt_id', '=', config('xmgl.yszt_ys'));      //config('xmgl.yszt_ys')：已验收
                }

                //回款状态
                $hkzt_id = empty($filter['hkzt_id']) ? '' : $filter['hkzt_id'];
                if (!empty($yszt)) {
                    $query->where('a.hkzt_id', '=', $hkzt_id);
                }
            })
            ->order($order)
            ->paginate([
                'list_rows' => $pageSize,
                'page' => $pageNumber,
            ]);

        dump($q->getLastSql());

        $rowTotal = $list->total();
        $j = [
            'pageSize'          => $pageSize,
            'pageNumber'        => $pageNumber,
            "totalPage"         => ceil($rowTotal / $pageSize),
            'total'             => $rowTotal,
            'rows'              => $list,
            'msg'               => ''
        ];

        return $list;
    }
}
